<!doctype html>



  


<html class="theme-next mist use-motion">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>



<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />












  <link href="/vendors/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css"/>




  <link href="//fonts.googleapis.com/css?family=Lato:300,400,700,400italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">



<link href="/vendors/font-awesome/css/font-awesome.min.css?v=4.4.0" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=0.5.0" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="关系型数据库,MySQL," />





  <link rel="alternate" href="/atom.xml" title="Joshua Shaw之秘密花园" type="application/atom+xml" />




  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=0.5.0" />






<meta name="description" content="0x00前言本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。

0x01简介良好的逻辑设计和物理设计是高性能的基石。Schema的设计是极为重要的，下面将会简单介绍具体情况下适合的设计。

0x02选择数据类型
更小的通常更好。这样会占用更少的磁盘，内存和CPU
简单">
<meta property="og:type" content="article">
<meta property="og:title" content="《高性能MySQL》のSchema与数据类型优化">
<meta property="og:url" content="http://joshshaw.github.io/2016/04/28/《高性能MySQL》のSchema与数据类型优化/index.html">
<meta property="og:site_name" content="Joshua Shaw之秘密花园">
<meta property="og:description" content="0x00前言本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。

0x01简介良好的逻辑设计和物理设计是高性能的基石。Schema的设计是极为重要的，下面将会简单介绍具体情况下适合的设计。

0x02选择数据类型
更小的通常更好。这样会占用更少的磁盘，内存和CPU
简单">
<meta property="og:updated_time" content="2016-04-28T11:23:01.665Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="《高性能MySQL》のSchema与数据类型优化">
<meta name="twitter:description" content="0x00前言本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。

0x01简介良好的逻辑设计和物理设计是高性能的基石。Schema的设计是极为重要的，下面将会简单介绍具体情况下适合的设计。

0x02选择数据类型
更小的通常更好。这样会占用更少的磁盘，内存和CPU
简单">



<script type="text/javascript" id="hexo.configuration">
  var NexT = window.NexT || {};
  var CONFIG = {
    scheme: 'Mist',
    sidebar: {"position":"left","display":"always"},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: 0,
      author: '博主'
    }
  };
</script>

  <title> 《高性能MySQL》のSchema与数据类型优化 | Joshua Shaw之秘密花园 </title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  



  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?99d95abecc387f2aa83d123804dddd4c";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>








  
  
    
  

  <div class="container one-collumn sidebar-position-left page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-meta ">
  

  <div class="custom-logo-site-title">
    <a href="/"  class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <span class="site-title">Joshua Shaw之秘密花园</span>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>
  <p class="site-subtitle">成就自己</p>
</div>

<div class="site-nav-toggle">
  <button>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
  </button>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-home fa-fw"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-user fa-fw"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories" rel="section">
            
              <i class="menu-item-icon fa fa-th fa-fw"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-archive fa-fw"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-tags fa-fw"></i> <br />
            
            标签
          </a>
        </li>
      

      
    </ul>
  

  
</nav>

 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                《高性能MySQL》のSchema与数据类型优化
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-04-28T13:41:45+08:00" content="2016-04-28">
              2016-04-28
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/计算机科学/" itemprop="url" rel="index">
                    <span itemprop="name">计算机科学</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/2016/04/28/《高性能MySQL》のSchema与数据类型优化/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/04/28/《高性能MySQL》のSchema与数据类型优化/" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        <h2 id="0x00前言"><a href="#0x00前言" class="headerlink" title="0x00前言"></a>0x00前言</h2><p>本书讲述到定稿前的MySQL5.5版，所以下面内容的适用范围止步于MySQL5.5。本文仅仅强调书中讲述的重中之重，<br>以便快速查阅，详细的内容还请认真阅读书本和MySQL的官方文档。</p>
<hr>
<h2 id="0x01简介"><a href="#0x01简介" class="headerlink" title="0x01简介"></a>0x01简介</h2><p>良好的逻辑设计和物理设计是高性能的基石。Schema的设计是极为重要的，下面将会简单介绍具体情况下适合的设计。</p>
<hr>
<h2 id="0x02选择数据类型"><a href="#0x02选择数据类型" class="headerlink" title="0x02选择数据类型"></a>0x02选择数据类型</h2><ul>
<li>更小的通常更好。这样会占用更少的磁盘，内存和CPU</li>
<li>简单就好。日期用日期类型DATETIME、TIMESTAMP而不是字符串等等。</li>
<li>尽量避免NULL。如果该列上建立索引，那么应该尽量避免NULL，但是也不能全不用NULL。</li>
</ul>
<h3 id="整数类型"><a href="#整数类型" class="headerlink" title="整数类型"></a>整数类型</h3><table>
<thead>
<tr>
<th style="text-align:center">类型</th>
<th style="text-align:center">TINYINT</th>
<th style="text-align:center">SMALLINT</th>
<th style="text-align:center">MEDIUMINT</th>
<th style="text-align:center">INT</th>
<th style="text-align:center">BIGINT</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">比特</td>
<td style="text-align:center">8</td>
<td style="text-align:center">16</td>
<td style="text-align:center">24</td>
<td style="text-align:center">32</td>
<td style="text-align:center">64</td>
</tr>
</tbody>
</table>
<p>字段非负时最好用上UNSIGNED。标识符一般使用整数字段，并且金钱一般也使用整数来存储，只需要乘以响应的<br>位数，这样计算上不需要使用float或者double，计算速度更快，并且不失精确性。而IPv4地址也最好用整数存储。</p>
<h3 id="实数类型"><a href="#实数类型" class="headerlink" title="实数类型"></a>实数类型</h3><p>DECIMAL类型也能精确计算，但是代缴较高，能用整数代替时最好用整数。</p>
<h3 id="字符串类型"><a href="#字符串类型" class="headerlink" title="字符串类型"></a>字符串类型</h3><p>字符串类型用较多种，如VARCHAR、CHAR、TEXT、BOLB等。<br>数据库、表、和表中的列都可以定义自己的字符集和排序规则，但是一般情况下最好统一，这样能简化开发。<br>字符长度不要分配太多，应该分配适合范围内的最小值。<br><a id="more"></a></p>
<h4 id="VARCHAR"><a href="#VARCHAR" class="headerlink" title="VARCHAR"></a>VARCHAR</h4><p>VARCHAR需要使用1或者2个额外字节记录字符串的长度，并且要避免使用<code>ROW_FORMAT=FIXED</code>。能节省存储空间，<br>但是UPDATE时会耗时，于是需要时常更新的列不宜使用。新版本的InnoDB会自动将过长的VARCHAR存储为BLOB。<br>下面场景适合使用：</p>
<ul>
<li>当字符串列的最大长度比平均长度大很多</li>
<li>列更新少</li>
<li>使用UTF-8字符集</li>
</ul>
<h4 id="CHAR"><a href="#CHAR" class="headerlink" title="CHAR"></a>CHAR</h4><p>CHAR类型是定长的，不足时会填充字符，不会产生碎片。新版本的MySQL会去掉最后的空格。下面场景适合使用：</p>
<ul>
<li>存储密码的MD5等散列</li>
<li>时常变更的数据</li>
<li>非常短的列</li>
</ul>
<h4 id="BLOB和TEXT"><a href="#BLOB和TEXT" class="headerlink" title="BLOB和TEXT"></a>BLOB和TEXT</h4><p>前者没有排序规则或字符集，后者有。排序只与<code>max_sort-length</code>有关。</p>
<h4 id="枚举（ENUM）代替字符串"><a href="#枚举（ENUM）代替字符串" class="headerlink" title="枚举（ENUM）代替字符串"></a>枚举（ENUM）代替字符串</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> enum_test(</span><br><span class="line">  e ENUM(<span class="string">'fish'</span>, <span class="string">'apple'</span>, <span class="string">'dog'</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>实际存储的是整数。并且排序是根据整数而不是字符串排序的。添加和删除字符串必须使用ALTER TABLE。<br>当枚举的数量少并且不时常更改时适合使用，还可以减少整体的存储空间。</p>
<h3 id="日期和时间类型"><a href="#日期和时间类型" class="headerlink" title="日期和时间类型"></a>日期和时间类型</h3><p>DATETIME能保存大范围的值，TIMESTAMP只能到2038年，但是与时区有关，存储也更小，默认为NOT NULL。<br>推荐使用TIMESTAMP。</p>
<h3 id="位数据类型"><a href="#位数据类型" class="headerlink" title="位数据类型"></a>位数据类型</h3><p>技术上来说都是字符串类型。</p>
<h4 id="BIT"><a href="#BIT" class="headerlink" title="BIT"></a>BIT</h4><p>如果想用BIT来存储一个false/true值，能通过创建CHAR(0)来代替，其中NULL为false，空字符串为true。</p>
<h4 id="SET"><a href="#SET" class="headerlink" title="SET"></a>SET</h4><p>保存大量布尔值集合时使用，不过最好用整数类型的8/4/2/1来代替。</p>
<h3 id="选择标识符"><a href="#选择标识符" class="headerlink" title="选择标识符"></a>选择标识符</h3><p>像QQ号码就是一个标识符。最好使用整数而不是字符串（耗空间，慢）来实现，关联时关联字段的定义要保持一致。</p>
<h3 id="特殊类型数据"><a href="#特殊类型数据" class="headerlink" title="特殊类型数据"></a>特殊类型数据</h3><p>强烈推荐使用无符号整数存储IP地址，再通过MySQL的<code>INET_ATON()</code>和<code>INET_NTOA()</code>来转换。</p>
<hr>
<h2 id="0x03-设计陷阱"><a href="#0x03-设计陷阱" class="headerlink" title="0x03 设计陷阱"></a>0x03 设计陷阱</h2><p>以下的设计要尽量避免：</p>
<ul>
<li>太多的列。当需要使用的数据并不是全部时</li>
<li>太多的关联。太多关联会使得查询变慢</li>
<li>全能和变相的枚举。枚举尽量避免使用</li>
<li>哪里都不用NULL。有时候NULL是必须并且有效的</li>
</ul>
<hr>
<h2 id="0x04范式和反范式"><a href="#0x04范式和反范式" class="headerlink" title="0x04范式和反范式"></a>0x04范式和反范式</h2><h3 id="范式的优缺点"><a href="#范式的优缺点" class="headerlink" title="范式的优缺点"></a>范式的优缺点</h3><ul>
<li>更新操作快</li>
<li>没有重复数据</li>
<li>表通常更小，更好地放在内存里</li>
<li>更少地需要使用DISTINCT或者GROUP BY</li>
<li><strong>缺点是需要关联，使得一些索引策略无效</strong></li>
</ul>
<h3 id="反范式的优缺点"><a href="#反范式的优缺点" class="headerlink" title="反范式的优缺点"></a>反范式的优缺点</h3><ul>
<li>避免关联</li>
<li>当数据量大得比内存多时速度很快</li>
<li>避免随机IO</li>
<li><strong>缺点是更改字段时需要做更多，存储也更多</strong></li>
</ul>
<h3 id="混用范式和反范式"><a href="#混用范式和反范式" class="headerlink" title="混用范式和反范式"></a>混用范式和反范式</h3><p>排序和汇总时可以适当地使用反范式。</p>
<h3 id="缓存表和汇总表"><a href="#缓存表和汇总表" class="headerlink" title="缓存表和汇总表"></a>缓存表和汇总表</h3><p>如果能容忍少量的脏数据来增强实时性时可以使用。前者是逻辑上的冗余数据，后者在逻辑上并不冗余。</p>
<h3 id="计数器表"><a href="#计数器表" class="headerlink" title="计数器表"></a>计数器表</h3><p>加槽来提搞并发量，并且定时删除过多的列。</p>
<hr>
<h2 id="0x05加快ALTER-TABLE操作的速度"><a href="#0x05加快ALTER-TABLE操作的速度" class="headerlink" title="0x05加快ALTER TABLE操作的速度"></a>0x05加快ALTER TABLE操作的速度</h2><p>书上的做法并不可取，最好通过在备库上执行ALTER TABLE操作，再切换主备库来实现。</p>
<hr>
<h2 id="0x06小结"><a href="#0x06小结" class="headerlink" title="0x06小结"></a>0x06小结</h2><ul>
<li>尽量避免过度的设计</li>
<li>尽可能地避免使用NULL值，但也不能太过</li>
<li>尽量使用相同的数据类型存储相似或相关的值</li>
<li>注意可变长字符串，临时表和排序时可能会使用大量的内存</li>
<li>尽量使用整型定义标识列</li>
<li>小心使用ENUM和SET，最好避免使用BIT</li>
<li>通过在备库上执行ALTER TABLE操作，再切换主备库</li>
</ul>
<hr>
<h2 id="引用"><a href="#引用" class="headerlink" title="引用"></a>引用</h2><p><a href="https://book.douban.com/subject/4241826/" target="_blank" rel="external">《高性能MySQL · 第三版》</a></p>

      
    </div>

    <div>
      
        
      
    </div>

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/关系型数据库/" rel="tag">#关系型数据库</a>
          
            <a href="/tags/MySQL/" rel="tag">#MySQL</a>
          
        </div>
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2016/04/26/《高性能MySQL》の复制/" rel="next" title="《高性能MySQL》の复制">
                <i class="fa fa-chevron-left"></i> 《高性能MySQL》の复制
              </a>
            
          </div>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2016/04/29/《高性能MySQL》の创建高性能的索引/" rel="prev" title="《高性能MySQL》の创建高性能的索引">
                《高性能MySQL》の创建高性能的索引 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </article>



    <div class="post-spread">
      
        <!-- JiaThis Button BEGIN -->
<div class="jiathis_style">
  <a class="jiathis_button_tsina"></a>
  <a class="jiathis_button_tqq"></a>
  <a class="jiathis_button_weixin"></a>
  <a class="jiathis_button_cqq"></a>
  <a class="jiathis_button_douban"></a>
  <a class="jiathis_button_renren"></a>
  <a class="jiathis_button_qzone"></a>
  <a class="jiathis_button_kaixin001"></a>
  <a class="jiathis_button_copy"></a>
  <a href="http://www.jiathis.com/share" class="jiathis jiathis_txt jiathis_separator jtico jtico_jiathis" target="_blank"></a>
  <a class="jiathis_counter_style"></a>
</div>
<script type="text/javascript" >
  var jiathis_config={
    hideMore:false
  }
</script>
<script type="text/javascript" src="http://v3.jiathis.com/code/jia.js" charset="utf-8"></script>
<!-- JiaThis Button END -->

      
    </div>
  </div>


          </div>
          


          
  <div class="comments" id="comments">
    
      <div class="ds-thread" data-thread-key="2016/04/28/《高性能MySQL》のSchema与数据类型优化/"
           data-title="《高性能MySQL》のSchema与数据类型优化" data-url="http://joshshaw.github.io/2016/04/28/《高性能MySQL》のSchema与数据类型优化/">
      </div>
    
  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel ">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/images/avatar.png"
               alt="Joshua Shaw" />
          <p class="site-author-name" itemprop="name">Joshua Shaw</p>
          <p class="site-description motion-element" itemprop="description"></p>
        </div>
        <nav class="site-state motion-element">
          <div class="site-state-item site-state-posts">
            <a href="/archives">
              <span class="site-state-item-count">20</span>
              <span class="site-state-item-name">日志</span>
            </a>
          </div>

          
            <div class="site-state-item site-state-categories">
              <a href="/categories">
                <span class="site-state-item-count">6</span>
                <span class="site-state-item-name">分类</span>
              </a>
            </div>
          

          
            <div class="site-state-item site-state-tags">
              <a href="/tags">
                <span class="site-state-item-count">26</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        
          <div class="feed-link motion-element">
            <a href="/atom.xml" rel="alternate">
              <i class="fa fa-rss"></i>
              RSS
            </a>
          </div>
        

        <div class="links-of-author motion-element">
          
            
              <span class="links-of-author-item">
                <a href="https://github.com/joshuashawnetlab" target="_blank">
                  
                    <i class="fa fa-globe"></i> Github_1
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="https://github.com/joshshaw" target="_blank">
                  
                    <i class="fa fa-globe"></i> Github_2
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://weibo.com/u/2622463195" target="_blank">
                  
                    <i class="fa fa-weibo"></i> Weibo
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://my.oschina.net/joshuashaw" target="_blank">
                  
                    <i class="fa fa-globe"></i> OSChina
                  
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="https://www.zhihu.com/people/xiao-chong-chong-41" target="_blank">
                  
                    <i class="fa fa-globe"></i> Zhihu
                  
                </a>
              </span>
            
          
        </div>

        
        

        
        <div class="links-of-blogroll motion-element">
          
        </div>

      </section>

      
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc-indicator-top post-toc-indicator">
            <i class="fa fa-angle-double-up"></i>
          </div>
          <div class="post-toc">
            
              
            
            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#0x00前言"><span class="nav-number">1.</span> <span class="nav-text">0x00前言</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#0x01简介"><span class="nav-number">2.</span> <span class="nav-text">0x01简介</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#0x02选择数据类型"><span class="nav-number">3.</span> <span class="nav-text">0x02选择数据类型</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#整数类型"><span class="nav-number">3.1.</span> <span class="nav-text">整数类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#实数类型"><span class="nav-number">3.2.</span> <span class="nav-text">实数类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#字符串类型"><span class="nav-number">3.3.</span> <span class="nav-text">字符串类型</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#VARCHAR"><span class="nav-number">3.3.1.</span> <span class="nav-text">VARCHAR</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#CHAR"><span class="nav-number">3.3.2.</span> <span class="nav-text">CHAR</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#BLOB和TEXT"><span class="nav-number">3.3.3.</span> <span class="nav-text">BLOB和TEXT</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#枚举（ENUM）代替字符串"><span class="nav-number">3.3.4.</span> <span class="nav-text">枚举（ENUM）代替字符串</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#日期和时间类型"><span class="nav-number">3.4.</span> <span class="nav-text">日期和时间类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#位数据类型"><span class="nav-number">3.5.</span> <span class="nav-text">位数据类型</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#BIT"><span class="nav-number">3.5.1.</span> <span class="nav-text">BIT</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#SET"><span class="nav-number">3.5.2.</span> <span class="nav-text">SET</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#选择标识符"><span class="nav-number">3.6.</span> <span class="nav-text">选择标识符</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#特殊类型数据"><span class="nav-number">3.7.</span> <span class="nav-text">特殊类型数据</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#0x03-设计陷阱"><span class="nav-number">4.</span> <span class="nav-text">0x03 设计陷阱</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#0x04范式和反范式"><span class="nav-number">5.</span> <span class="nav-text">0x04范式和反范式</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#范式的优缺点"><span class="nav-number">5.1.</span> <span class="nav-text">范式的优缺点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#反范式的优缺点"><span class="nav-number">5.2.</span> <span class="nav-text">反范式的优缺点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#混用范式和反范式"><span class="nav-number">5.3.</span> <span class="nav-text">混用范式和反范式</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#缓存表和汇总表"><span class="nav-number">5.4.</span> <span class="nav-text">缓存表和汇总表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#计数器表"><span class="nav-number">5.5.</span> <span class="nav-text">计数器表</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#0x05加快ALTER-TABLE操作的速度"><span class="nav-number">6.</span> <span class="nav-text">0x05加快ALTER TABLE操作的速度</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#0x06小结"><span class="nav-number">7.</span> <span class="nav-text">0x06小结</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#引用"><span class="nav-number">8.</span> <span class="nav-text">引用</span></a></li></ol></div>
            
          </div>
          <div class="post-toc-indicator-bottom post-toc-indicator">
            <i class="fa fa-angle-double-down"></i>
          </div>
        </section>
      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy;  2015 - 
  <span itemprop="copyrightYear">2016</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Joshua Shaw</span>
</div>

<div class="powered-by">
  由 <a class="theme-link" href="http://hexo.io">Hexo</a> 强力驱动
</div>

<div class="theme-info">
  主题 -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next">
    NexT.Mist
  </a>
</div>



      </div>
    </footer>

    <div class="back-to-top">
      <i class="fa fa-arrow-up"></i>
    </div>
  </div>

  


  




<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>


  <script type="text/javascript" src="/vendors/jquery/index.js?v=2.1.3"></script>

  <script type="text/javascript" src="/vendors/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  <script type="text/javascript" src="/vendors/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  <script type="text/javascript" src="/vendors/velocity/velocity.min.js"></script>

  <script type="text/javascript" src="/vendors/velocity/velocity.ui.min.js"></script>

  <script type="text/javascript" src="/vendors/fancybox/source/jquery.fancybox.pack.js"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=0.5.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=0.5.0"></script>



  
  

  
  
<script type="text/javascript" src="/js/src/scrollspy.js?v=0.5.0"></script>

<script type="text/javascript" id="sidebar.toc.highlight">
  $(document).ready(function () {
    var tocSelector = '.post-toc';
    var $tocSelector = $(tocSelector);
    var activeCurrentSelector = '.active-current';

    $tocSelector
      .on('activate.bs.scrollspy', function () {
        var $currentActiveElement = $(tocSelector + ' .active').last();

        removeCurrentActiveClass();
        $currentActiveElement.addClass('active-current');

        $tocSelector[0].scrollTop = $currentActiveElement.position().top;
      })
      .on('clear.bs.scrollspy', function () {
        removeCurrentActiveClass();
      });

    function removeCurrentActiveClass () {
      $(tocSelector + ' ' + activeCurrentSelector)
        .removeClass(activeCurrentSelector.substring(1));
    }

    function processTOC () {
      getTOCMaxHeight();
      toggleTOCOverflowIndicators();
    }

    function getTOCMaxHeight () {
      var height = $('.sidebar').height() -
                   $tocSelector.position().top -
                   $('.post-toc-indicator-bottom').height();

      $tocSelector.css('height', height);

      return height;
    }

    function toggleTOCOverflowIndicators () {
      tocOverflowIndicator(
        '.post-toc-indicator-top',
        $tocSelector.scrollTop() > 0 ? 'show' : 'hide'
      );

      tocOverflowIndicator(
        '.post-toc-indicator-bottom',
        $tocSelector.scrollTop() >= $tocSelector.find('ol').height() - $tocSelector.height() ? 'hide' : 'show'
      )
    }

    $(document).on('sidebar.motion.complete', function () {
      processTOC();
    });

    $('body').scrollspy({ target: tocSelector });
    $(window).on('resize', function () {
      if ( $('.sidebar').hasClass('sidebar-active') ) {
        processTOC();
      }
    });

    onScroll($tocSelector);

    function onScroll (element) {
      element.on('mousewheel DOMMouseScroll', function (event) {
          var oe = event.originalEvent;
          var delta = oe.wheelDelta || -oe.detail;

          this.scrollTop += ( delta < 0 ? 1 : -1 ) * 30;
          event.preventDefault();

          toggleTOCOverflowIndicators();
      });
    }

    function tocOverflowIndicator (indicator, action) {
      var $indicator = $(indicator);
      var opacity = action === 'show' ? 1 : 0;
      $indicator.velocity ?
        $indicator.velocity('stop').velocity({
          opacity: opacity
        }, { duration: 100 }) :
        $indicator.stop().animate({
          opacity: opacity
        }, 100);
    }

  });
</script>

<script type="text/javascript" id="sidebar.nav">
  $(document).ready(function () {
    var html = $('html');
    var TAB_ANIMATE_DURATION = 200;
    var hasVelocity = $.isFunction(html.velocity);

    $('.sidebar-nav li').on('click', function () {
      var item = $(this);
      var activeTabClassName = 'sidebar-nav-active';
      var activePanelClassName = 'sidebar-panel-active';
      if (item.hasClass(activeTabClassName)) {
        return;
      }

      var currentTarget = $('.' + activePanelClassName);
      var target = $('.' + item.data('target'));

      hasVelocity ?
        currentTarget.velocity('transition.slideUpOut', TAB_ANIMATE_DURATION, function () {
          target
            .velocity('stop')
            .velocity('transition.slideDownIn', TAB_ANIMATE_DURATION)
            .addClass(activePanelClassName);
        }) :
        currentTarget.animate({ opacity: 0 }, TAB_ANIMATE_DURATION, function () {
          currentTarget.hide();
          target
            .stop()
            .css({'opacity': 0, 'display': 'block'})
            .animate({ opacity: 1 }, TAB_ANIMATE_DURATION, function () {
              currentTarget.removeClass(activePanelClassName);
              target.addClass(activePanelClassName);
            });
        });

      item.siblings().removeClass(activeTabClassName);
      item.addClass(activeTabClassName);
    });

    $('.post-toc a').on('click', function (e) {
      e.preventDefault();
      var targetSelector = NexT.utils.escapeSelector(this.getAttribute('href'));
      var offset = $(targetSelector).offset().top;
      hasVelocity ?
        html.velocity('stop').velocity('scroll', {
          offset: offset  + 'px',
          mobileHA: false
        }) :
        $('html, body').stop().animate({
          scrollTop: offset
        }, 500);
    });

    // Expand sidebar on post detail page by default, when post has a toc.
    NexT.motion.middleWares.sidebar = function () {
      var $tocContent = $('.post-toc-content');

      if (CONFIG.sidebar.display === 'post' || CONFIG.sidebar.display === 'always') {
        if ($tocContent.length > 0 && $tocContent.html().trim().length > 0) {
          NexT.utils.displaySidebar();
        }
      }
    };
  });
</script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=0.5.0"></script>



  

  
    
  

  <script type="text/javascript">
    var duoshuoQuery = {short_name:"joshuashaw"};
    (function() {
      var ds = document.createElement('script');
      ds.type = 'text/javascript';ds.async = true;
      ds.id = 'duoshuo-script';
      ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
      ds.charset = 'UTF-8';
      (document.getElementsByTagName('head')[0]
      || document.getElementsByTagName('body')[0]).appendChild(ds);
    })();
  </script>

  
    
  





  
  
  

  


</body>
</html>
